﻿
Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsFileEdit

    Dim FolderPath As String = FTPAddress + "Prospects"

    Private Sub Clear()
        Me.txtProspName.Clear()
        Me.txtFax.Clear()
        Me.txtcompanyEmail.Clear()
        Me.txtWebsite.Clear()
        Me.GridContactPersons.Rows.Clear()
        Me.txtAddress.Clear()
        Me.txtCoDetails.Clear()
        Me.txtProspName.Focus()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Public Sub FillDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.txtProspID.Text)
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            ''list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.FullName, Fi.Name, "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub FillProspectDetails()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader

            cmd.Connection = cnn

            cnn.Open()

            '1) Fill prospect details
            cmd.CommandText = "Select * From Prospects Where SNo=" & Me.txtProspID.Text
            reader = cmd.ExecuteReader
            While reader.Read
                Me.txtProspName.Text = reader.Item("Name")
                Me.txtFax.Text = reader.Item("Fax")
                Me.txtTele.Text = reader.Item("Tele")
                Me.txtWebsite.Text = reader.Item("Website")
                Me.txtcompanyEmail.Text = reader.Item("Email")
                Me.txtAddress.Text = reader.Item("Address")
                Me.txtCoDetails.Text = reader.Item("CoRegDetails")
                Me.txtMobile.Text = reader.Item("Mobile")

            End While
            reader.Close()

            '2) Fill contact persons
            cmd.CommandText = "Select * From ProspectContacts Where ProspID=" & Me.txtProspID.Text
            reader = cmd.ExecuteReader
            While reader.Read
                Me.GridContactPersons.Rows.Add(New String() {reader.Item("ContactName"), reader.Item("Position"), reader.Item("Email"), _
                                                             reader.Item("Tele"), "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridContactPersons_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridContactPersons.CellClick
        If e.ColumnIndex = 4 Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Me.GridContactPersons.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub btnSave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        Try
            If Me.txtcompanyEmail.Text.Trim.Length > 0 And _
       Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtcompanyEmail.Text.Trim, _
                                                        "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
                Me.ErrProv.SetError(Me.txtcompanyEmail, "Please enter a valid email address or leave blank")
                Me.txtcompanyEmail.Focus()
                Exit Sub

            Else
                Me.Cursor = Cursors.WaitCursor

                Dim Trans As SqlTransaction
                Dim cmd As New SqlCommand()

                cnn.Open()
                cmd.Connection = cnn
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Insert prospect details
                cmd.Parameters.Clear()
                cmd.CommandText = "Update Prospects Set Branch=@Branch,Name=@Name,Tele=@Tele,Mobile=@Mobile,Fax=@Fax,Email=@Email," &
                    "Website=@Website,Address=@Address," & _
                    "CoRegDetails=@CoRegDetails,UserName=@UserName Where SNo =" & Me.txtProspID.Text

                cmd.Parameters.AddWithValue("@Branch", Branch)
  cmd.Parameters.AddWithValue("@Name", Me.txtProspName.Text.Trim)
                cmd.Parameters.AddWithValue("@Tele", Me.txtTele.Text.Trim)
                cmd.Parameters.AddWithValue("@Mobile", Me.txtMobile.Text.Trim)
                cmd.Parameters.AddWithValue("@Fax", Me.txtFax.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtcompanyEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@Website", Me.txtWebsite.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
                cmd.Parameters.AddWithValue("@CoRegDetails", Me.txtCoDetails.Text.Trim)
                cmd.Parameters.AddWithValue("@UserName", CurrentUser)
                cmd.ExecuteNonQuery()

                'Insert contact persons details
                '1) Delete old records
                cmd.CommandText = "Delete From ProspectContacts Where ProspID=" & Me.txtProspID.Text
                cmd.ExecuteNonQuery()

                'Save new records
                For Each row As DataGridViewRow In GridContactPersons.Rows
                    If Not row.IsNewRow Then
                        cmd.Parameters.Clear()
                        cmd.CommandText = "Insert Into ProspectContacts (ProspID,ContactName,Position,Email,Tele,SavedUser) Values (@ProspID,@ContactName,@Position,@Email,@Tele,@SavedUser)"
                        cmd.Parameters.AddWithValue("@ProspID", Me.txtProspID.Text)
                        cmd.Parameters.AddWithValue("@ContactName", row.Cells("ContactPerson").Value)
                        cmd.Parameters.AddWithValue("@Position", row.Cells("Position").Value)
                        cmd.Parameters.AddWithValue("@Email", row.Cells("Email").Value)
                        cmd.Parameters.AddWithValue("@Tele", row.Cells("Tele").Value)
                        cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Updated Successfuly")

                Me.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub btnInsertDoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsertDoc.Click
        Try
            Me.OpenFileDialog1.Filter = "All files (*.*)|*.*"
            If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim File As New FileInfo(Me.OpenFileDialog1.FileName)
                File.CopyTo(FolderPath & "\" & Me.txtProspID.Text & "\" & File.Name)

                FillDocuments()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = Me.DGFilesColDocOpen.Index Then
            Try
                Me.Cursor = Cursors.WaitCursor

                System.Diagnostics.Process.Start(Me.GridFiles.CurrentRow.Cells("DGFilesPath").Value)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGFilesColDocDel.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Me.GridFiles.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub frmProspectsFileEdit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class